<?php

namespace app\admin\controller\school;

use app\common\controller\Backend;
use think\Db;
use PhpOffice\PhpSpreadsheet\Cell\Coordinate;
use PhpOffice\PhpSpreadsheet\IOFactory;
use PhpOffice\PhpSpreadsheet\Reader\Xlsx;
use PhpOffice\PhpSpreadsheet\Reader\Xls;
use PhpOffice\PhpSpreadsheet\Reader\Csv;
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Style\Alignment;
use PhpOffice\PhpSpreadsheet\Style\Border;
use think\Exception;
use think\exception\PDOException;
use think\exception\ValidateException;
/**
 * 成绩管理
 *
 * @icon fa fa-circle-o
 */
class Grade extends Backend
{
    
    /**
     * Grade模型对象
     * @var \app\admin\model\school\Grade
     */
    protected $model = null;


    public function _initialize()
    {
        parent::_initialize();
        $this->model = new \app\admin\model\school\Grade;
        $this->view->assign("majorLevelDictList", $this->model->getMajorLevelDictList());
        $this->assignconfig("majorLevelDictList", $this->model->getMajorLevelDictList());
        $this->view->assign("termDictList", $this->model->getTermDictList());
        $this->assignconfig("termDictList", $this->model->getTermDictList());
        $this->view->assign("sexList", $this->model->getSexList());
        $this->view->assign("isBukaoList", $this->model->getIsBukaoList());
    }
    
    /**
     * 默认生成的控制器所继承的父类中有index/add/edit/del/multi五个基础方法、destroy/restore/recyclebin三个回收站方法
     * 因此在当前控制器中可不用编写增删改查的代码,除非需要自己控制这部分逻辑
     * 需要将application/admin/library/traits/Backend.php中对应的方法复制到当前控制器,然后进行修改
     */
    
    // /**
    //  * 添加
    //  */
    public function addBk()
    {
        if ($this->request->isPost()) {
            $params = $this->request->post("row/a");
            if ($params) {
                $params = $this->preExcludeFields($params);

                if ($this->dataLimit && $this->dataLimitFieldAutoFill) {
                    $params[$this->dataLimitField] = $this->auth->id;
                }
                $result = false;
                Db::startTrans();
                try {
                    //是否采用模型验证
                    if ($this->modelValidate) {
                        $name = str_replace("\\model\\", "\\validate\\", get_class($this->model));
                        $validate = is_bool($this->modelValidate) ? ($this->modelSceneValidate ? $name . '.add' : $name) : $this->modelValidate;
                        $this->model->validateFailException(true)->validate($validate);
                    }

                    $grade = $this->model->where([
                        'student_id' => $params['student_id'],
                        'lesson_id' => $params['lesson_id'],
                        'term_dict' => $params['term_dict'],
                    ])->find();

                    if ($grade) {
                        $result = $grade->save([
                            'cx_score' => $params['cx_score'],
                            'bk_score' => $params['bk_score'],
                            'is_bukao' => 1,
                        ]);
                    }else{
                        $this->error("成绩不存在");
                    }

//                    $result = $this->model->allowField(true)->save($params);
                    Db::commit();
                } catch (ValidateException $e) {
                    Db::rollback();
                    $this->error($e->getMessage());
                } catch (PDOException $e) {
                    Db::rollback();
                    $this->error($e->getMessage());
                } catch (Exception $e) {
                    Db::rollback();
                    $this->error($e->getMessage());
                }
                if ($result !== false) {
                    $this->success();
                } else {
                    $this->error(__('No rows were inserted'));
                }
            }
            $this->error(__('Parameter %s can not be empty', ''));
        }
        return $this->view->fetch();
    }

    /**
     * 查看
     */
    public function index()
    {
        //设置过滤方法
        $this->request->filter(['strip_tags']);
        if ($this->request->isAjax()) {
            //如果发送的来源是Selectpage，则转发到Selectpage
            if ($this->request->request('keyField')) {
                return $this->selectpage();
            }
            list($where, $sort, $order, $offset, $limit) = $this->buildparams(null,true);
            // $newSort = [];
            // foreach (explode(',', $sort) as $item) {
            //     $newSort[] = 'f.' . $item;
            // }
            // $sort = implode($newSort);
            $total = $this->model->field("f.*")->alias("f")->join("student s", "s.id = f.student_id")
                ->where($where)
                ->order($sort, $order)
//                ->buildSql();
                ->count();



            $list = $this->model
                ->field("f.*")
                ->alias("f")->join("student s", "s.id = f.student_id")
                ->where($where)
                ->order($sort, $order)
                ->limit($offset, $limit)
//                ->buildSql();
                ->select();

            $list = collection($list)->toArray();
            $result = array("total" => $total, "rows" => $list);

            return json($result);
        }
        return $this->view->fetch();
    }

    /**
     * 导入
     */
    public function import()
    {
        $file = $this->request->request('file');
        if (!$file) {
            $this->error(__('Parameter %s can not be empty', 'file'));
        }
        $filePath = ROOT_PATH . DS . 'public' . DS . $file;
        if (!is_file($filePath)) {
            $this->error(__('No results were found'));
        }
        //实例化reader
        $ext = pathinfo($filePath, PATHINFO_EXTENSION);
        if (!in_array($ext, ['csv', 'xls', 'xlsx'])) {
            $this->error(__('Unknown data format'));
        }
        if ($ext === 'csv') {
            $file = fopen($filePath, 'r');
            $filePath = tempnam(sys_get_temp_dir(), 'import_csv');
            $fp = fopen($filePath, "w");
            $n = 0;
            while ($line = fgets($file)) {
                $line = rtrim($line, "\n\r\0");
                $encoding = mb_detect_encoding($line, ['utf-8', 'gbk', 'latin1', 'big5']);
                if ($encoding != 'utf-8') {
                    $line = mb_convert_encoding($line, 'utf-8', $encoding);
                }
                if ($n == 0 || preg_match('/^".*"$/', $line)) {
                    fwrite($fp, $line . "\n");
                } else {
                    fwrite($fp, '"' . str_replace(['"', ','], ['""', '","'], $line) . "\"\n");
                }
                $n++;
            }
            fclose($file) || fclose($fp);

            $reader = new Csv();
        } elseif ($ext === 'xls') {
            $reader = new Xls();
        } else {
            $reader = new Xlsx();
        }

        //导入文件首行类型,默认是注释,如果需要使用字段名称请使用name
        $importHeadType = isset($this->importHeadType) ? $this->importHeadType : 'comment';

        $table = $this->model->getQuery()->getTable();
        $database = \think\Config::get('database.database');
        $fieldArr = [];
        $list = db()->query("SELECT COLUMN_NAME,COLUMN_COMMENT FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = ? AND TABLE_SCHEMA = ?", [$table, $database]);
        foreach ($list as $k => $v) {
            if ($importHeadType == 'comment') {
                $fieldArr[$v['COLUMN_COMMENT']] = $v['COLUMN_NAME'];
            } else {
                $fieldArr[$v['COLUMN_NAME']] = $v['COLUMN_NAME'];
            }
        }

        //加载文件
        $insert = [];
        try {
            if (!$PHPExcel = $reader->load($filePath)) {
                $this->error(__('Unknown data format'));
            }
            $currentSheet = $PHPExcel->getSheet(0);  //读取文件中的第一个工作表
            $allColumn = $currentSheet->getHighestDataColumn(); //取得最大的列号
            $allRow = $currentSheet->getHighestRow(); //取得一共有多少行
            $maxColumnNumber = Coordinate::columnIndexFromString($allColumn);
            $fields = [];
            for ($currentRow = 1; $currentRow <= 1; $currentRow++) {
                for ($currentColumn = 1; $currentColumn <= $maxColumnNumber; $currentColumn++) {
                    $val = $currentSheet->getCellByColumnAndRow($currentColumn, $currentRow)->getValue();
                    $fields[] = $val;
                }
            }

            for ($currentRow = 2; $currentRow <= $allRow; $currentRow++) {
                $values = [];
                for ($currentColumn = 1; $currentColumn <= $maxColumnNumber; $currentColumn++) {
                    $val = $currentSheet->getCellByColumnAndRow($currentColumn, $currentRow)->getValue();
                    $values[] = is_null($val) ? '' : $val;
                }
                $row = [];
                $temp = array_combine($fields, $values);
                foreach ($temp as $k => $v) {
                    if (isset($fieldArr[$k]) && $k !== '') {
                        $row[$fieldArr[$k]] = $v;
                    }
                }
                if ($row) {
                    $insert[] = $row;
                }
            }
        } catch (Exception $exception) {
            $this->error($exception->getMessage());
        }
        if (!$insert) {
            $this->error(__('No rows were updated'));
        }

        try {
            //是否包含admin_id字段
            $has_admin_id = false;
            foreach ($fieldArr as $name => $key) {
                if ($key == 'admin_id') {
                    $has_admin_id = true;
                    break;
                }
            }
            if ($has_admin_id) {
                $auth = Auth::instance();
                foreach ($insert as &$val) {
                    if (!isset($val['admin_id']) || empty($val['admin_id'])) {
                        $val['admin_id'] = $auth->isLogin() ? $auth->id : 0;
                    }
                }
            }

            // mct mact 找到下拉对应的 数据库中的值
            foreach ($insert as &$val) {
                foreach ($val as $k => $item) {
                    if($k == "student_id"){
                        $m = substr($k, 0, strpos($k, "_id"));
                        $mo = \think\Loader::model(''. $m,'school',false,"admin\\model")->where("student_number", $item)->find();
                        $val[$k] = $mo['id']; 
                        $val['student_name'] = $mo['name']; 
                        $val['major_id'] = $mo['major_id']; 
                        $val['sex'] = $mo['student_sex']; 
                        $val['major_level_dict'] = $mo['major_level_dict']; 
                        $val['level'] = $mo['exametime'] + 1; 
                    }
                    if($k == "lesson_id"){ 
                        $count = strlen($k);
                        if (strpos($k, "_id") && strpos($k, "_id") == $count -3) { 
                                $m = substr($k, 0, strpos($k, "_id"));
                                $mo = \think\Loader::model(''. $m,'school',false,"admin\\model")->field("id")->where("name", $item)->find();
                                $val[$k] = $mo['id']; 
                        } 
                    }
                    if($k == "term_dict"){ 
                            $method = $this->getCamelizeName('get_' . $k). "List";
    
                            $map = $this->model->$method();
                            $map = array_flip($map);
    
                            $val[$k] = $map[trim($val[$k])];
                    }
                }

            }  
            $this->model->saveAll($insert);
        } catch (PDOException $exception) {
            $msg = $exception->getMessage();
            if (preg_match("/.+Integrity constraint violation: 1062 Duplicate entry '(.+)' for key '(.+)'/is", $msg, $matches)) {
                $msg = "导入失败，重复导入，包含【{$matches[1]}】的记录已存在";
            }else{
                $msg = "导入失败，字段错误，请检查导入数据是否符合规范";
            };
            $this->error($msg);
        } catch (Exception $e) {
            $msg = "导入失败，字段错误，请检查导入数据是否符合规范";
            $this->error($msg); 
        }

        $this->success();
    }
    /**
     * 导出毕业生成绩登记表，空白表
     */
    public function exportModel()
    {
        
        $spreadsheet = new Spreadsheet; //创建一个sheet页
        $sheet = $spreadsheet->getActiveSheet(); //获取一个可以操作的sheet页
        //设置sheet的名字  两种方法
        $sheet->setTitle('sheet1'); //设置一张sheet页的字
        $exportData = [
            'A1:J1' => [['1', ''], '学     习     成     绩'],
            'A2:A3' => [['2', 'warpText'], '序号'],
            'B2:B3' => [['2', ''], '课程'],
            'C2:C3' => [['2', ''], '学时'],
            'D2:D3' => [['2', ''], '学分'],
            'E2:E3' => [['2', ''], '成绩'],
            'F2:F3' => [['2', 'warpText'], '序号'],
            'G2:G3' => [['2', ''], '课程'],
            'H2:H3' => [['2', ''], '学时'],
            'I2:I3' => [['2', ''], '学分'],
            'J2:J3' => [['2', ''], '成绩'],
            'F21:G21' => [['2', ''], '合   计'],
            'H21' => [['2', ''], ' '],
            'I21' => [['2', ''], ' '],
            'J21' => [['2', ''], '——'],
            'A22:B22' => [['5', ''], '毕业论文'],
            'A23:B23' => [['5', ''], '或'],
            'A24:B24' => [['5', ''], '毕业设计'],
            'C22:J23' => [['3', ''], '题目：'],
            'C24:J24' => [['2', ''], '评定成绩：                                 指导教师：']

        ];
        $i = 1;
        //每学期的标题
        for ($j = 4; $j <= 21; $j++) {
            $list = [
                'A' . $j => [['4', ''], $i],
                'B' . $j => [['4', ''], ''],
                'C' . $j => [['4', ''], ''],
                'D' . $j => [['4', ''], ''],
                'E' . $j => [['4', ''], ''],
            ];
            $i++;
            $exportData += $list; //添加数组
        }
        for ($j = 4; $j <= 20; $j++) {
            $list = [
                'F' . $j => [['4', ''], $i],
                'G' . $j => [['4', ''], ''],
                'H' . $j => [['4', ''], ''],
                'I' . $j => [['4', ''], ''],
                'J' . $j => [['4', ''], ''],
            ];
            $i++;
            $exportData += $list; //添加数组
        }
        $this->exportStyle3($exportData, $sheet);

        $file_name = date('Y-m-d', time()) . rand(1000, 9999);
        //第二种直接页面上显示下载
        $file_name = '毕业生登记表成绩单'  . ".xls";
        //        header('Content-Type: application/vnd.ms-excel');
        header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
        header('Content-Disposition: attachment;filename="' . $file_name . '"');
        header('Cache-Control: max-age=0');
        $writer = IOFactory::createWriter($spreadsheet, 'Xls');
        //注意createWriter($spreadsheet, 'Xls') 第二个参数首字母必须大写
        $writer->save('php://output');
        exit;
    }

    function exportStyle3($exportData = [], $sheet)
    {
        $sheet->getColumnDimension('A')->setWidth(4);
        $sheet->getColumnDimension('B')->setWidth(20);
        $sheet->getColumnDimension('C')->setWidth(8);
        $sheet->getColumnDimension('D')->setWidth(8);
        $sheet->getColumnDimension('E')->setWidth(10);
        $sheet->getColumnDimension('F')->setWidth(4);
        $sheet->getColumnDimension('G')->setWidth(20);
        $sheet->getColumnDimension('H')->setWidth(8);
        $sheet->getColumnDimension('I')->setWidth(8);
        $sheet->getColumnDimension('J')->setWidth(10);
        //     //每行单元格的高
        $sheet->getRowDimension(2)->setRowHeight(20);
        $sheet->getRowDimension(3)->setRowHeight(20);
        for ($x = 4; $x <= 21; $x++) {
            $sheet->getRowDimension($x)->setRowHeight(20);
        }

        //正文加粗字体
        $font1 =  [
            'alignment' => [
                'horizontal' => Alignment::HORIZONTAL_CENTER, //水平居中
                'vertical' => Alignment::VERTICAL_CENTER, //上下居中
            ],
            'font' => [
                'name' => '仿宋_GB2312',
                'bold' => true,
                'size' => 28,
            ]
        ];
        //正文普通字体
        $font2 =  [
            'alignment' => [
                'horizontal' => Alignment::HORIZONTAL_CENTER, //水平居中
                'vertical' => Alignment::VERTICAL_CENTER, //上下居中
            ],
            'font' => [
                'name' => '仿宋_GB2312',
                'size' => 12,
            ],
            'borders' => [ //设置边框
                'outline' => [
                    'borderStyle' => Border::BORDER_THIN,
                    'color' => ['argb' => '000000']
                ],
            ]
        ];

        //正文普通小字体
        $font3 =  [
            'alignment' => [
                'horizontal' => Alignment::HORIZONTAL_LEFT, //水平居中
                'vertical' => Alignment::VERTICAL_TOP, //上下居中
            ],
            'font' => [
                'name' => '仿宋_GB2312',
                'size' => 12,
            ],
            'borders' => [ //设置边框
                'outline' => [
                    'borderStyle' => Border::BORDER_THIN,
                    'color' => ['argb' => '000000']
                ],
            ]
        ];
        //列名
        $font4 =  [
            'alignment' => [
                'horizontal' => Alignment::HORIZONTAL_CENTER, //水平居中
                'vertical' => Alignment::VERTICAL_CENTER, //上下居中
            ],
            'font' => [
                'name' => '仿宋_GB2312',
                'size' => 12,
                'bold' => true,
            ],
            'borders' => [ //设置边框
                'outline' => [
                    'borderStyle' => Border::BORDER_THIN,
                    'color' => ['argb' => '000000']
                ],
            ]
        ];
        //底下文字
        $font5 = [
            'alignment' => [
                'horizontal' => Alignment::HORIZONTAL_CENTER, //水平居中
                'vertical' => Alignment::VERTICAL_CENTER, //上下居中
            ],
            'font' => [
                'name' => '仿宋_GB2312',
                'size' => 12,
            ]
        ];
        //正文
        $font6 =  [
            'alignment' => [
                'horizontal' => Alignment::HORIZONTAL_CENTER, //水平居中
                'vertical' => Alignment::VERTICAL_CENTER, //上下居中
            ],
            'font' => [
                'name' => '仿宋_GB2312',

                'size' => 13,
            ],
            'borders' => [ //设置边框
                'outline' => [
                    'borderStyle' => Border::BORDER_THIN,
                    'color' => ['argb' => '000000']
                ],
            ]
        ];
        $font7 =  [
            'alignment' => [
                'horizontal' => Alignment::HORIZONTAL_LEFT, //水平居左
                'vertical' => Alignment::VERTICAL_CENTER, //上下居中
            ],
            'font' => [
                'name' => '仿宋_GB2312',
                'bold' => true,
                'size' => 9,
            ],
            'borders' => [ //设置边框
                'outline' => [
                    'borderStyle' => Border::BORDER_THIN,
                    'color' => ['argb' => '000000']
                ],
            ]
        ];
        foreach ($exportData as $key => $value) {
            $arr = explode(':', $key);
            if (count($arr) == 2) { //融合多單元格
                $sheet->mergeCells($key); //跟据key融合
                $sheet->setCellValue($arr[0], $value[1]); //設置值
            } else { //单个单元格

                $sheet->setCellValue($key, $value[1]); //設置值
            }

            switch ($value[0][0]) { //設置樣式
                case '1':
                    $sheet->getStyle($key)->applyFromArray($font1);
                    break;
                case '2':
                    $sheet->getStyle($key)->applyFromArray($font2);
                    break;
                case '3':
                    $sheet->getStyle($key)->applyFromArray($font3);
                    break;
                case '4':
                    $sheet->getStyle($key)->applyFromArray($font4);
                    break;
                case '5':
                    $sheet->getStyle($key)->applyFromArray($font5);
                    break;
                case '6':
                    $sheet->getStyle($key)->applyFromArray($font6);
                    break;
                case '7':
                    $sheet->getStyle($key)->applyFromArray($font7);
                    break;
                default:
                    $sheet->getStyle($key)->applyFromArray($font2);
                    break;
            }
            if ($value[0][1] == 'warpText') {
                $sheet->getStyle($key)->getAlignment()->setWrapText(true); //自动换行
            }
        }
        //外围边框
        $sheet->getStyle('A2:J24')->applyFromArray([
            'borders' => [ //设置边框
                'outline' => [
                    'borderStyle' => Border::BORDER_MEDIUM,
                    'color' => ['argb' => '000000']
                ],
            ],

        ]);
    }


    
    // function sheetStyle($sheet)
    // {
    //     $sheet->getColumnDimension('J')->setWidth(30);
    // }
    
}